package ext.tianma.bom.util;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ptc.core.logging.Log;
import com.ptc.core.logging.LogFactory;



import wt.method.MethodContext;
import wt.pom.Transaction;
import wt.pom.WTConnection;
/**
 * 
 * @author GeAiping
 * @version 1.0
 */
public class DBUtil {
    private static Log LCSLog=LogFactory.getLog(DBUtil.class);
    public static String CLASS_NAME = "ext.tianma.bom.util.DBUtil";
    /**
     * get Nextval
     * @param seqName
     * @param length
     * @return
     */
    public static String getNextValue(String seqName,int length){
        String nextValue="";
        WTConnection wtconnection = null;
        try {
            MethodContext methodcontext = MethodContext.getContext();
            wtconnection = (WTConnection) methodcontext.getConnection();
            if (!isSequenceExist(wtconnection, seqName)) {
                createSequence(wtconnection, seqName);
            }
            nextValue = getNextValBySeqName(wtconnection, seqName,
                    length);
            wtconnection.release();
            wtconnection = null;
        } catch (SQLException e) {
            LCSLog.error(CLASS_NAME + "$$$$getSupplierSequence$$$$"
                    + e.getMessage());
        } catch (Exception e) {
            LCSLog.error(CLASS_NAME + "$$$$getSupplierSequence$$$$"
                    + e.getMessage());
        } finally {
            try {
                if (wtconnection != null) {
                    wtconnection.release();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return nextValue;
        
    }
    
    /**
     * get next value
     * @param wtConn
     * @param seqName
     * @param seqLength
     * @return
     */
    public static String getNextValBySeqName(WTConnection wtConn,
            String seqName, int seqLength) {
        String querySQL = "SELECT lpad(" + seqName.toUpperCase() + ".NEXTVAL ,"
                + seqLength + ",'0') as seqvalue FROM DUAL";
        
        //String querySQL = "SELECT " + seqName.toUpperCase() + ".NEXTVAL FROM DUAL";
        //log.info("querySQL:"+querySQL);
        String nextValue = "000";
        try {
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            pstmt = wtConn.prepareStatement(querySQL);
            //pstmt.setString(1, seqName.toUpperCase());
            rs = pstmt.executeQuery();
            if (rs.next()) {
                nextValue = rs.getString(1);
            }
            //log.info("nextValue:+"+nextValue);
            LCSLog.debug(CLASS_NAME + "$$$$getNextValBySeqName$$$$" + " next:"
                    + nextValue);
            rs.close();
            rs = null;

            pstmt.close();
            pstmt = null;
        } catch (SQLException e) {
            LCSLog.error(CLASS_NAME+".getNextValBySeqName"+" SQL Error Code" +e.getErrorCode()+ "$$$$getNextValBySeqName$$$$"
                    + e.getMessage());
        }
        return nextValue;
    }
    
    
    public static String getNextValue(String seqName) {
        WTConnection wtconnection = null;
        String querySQL = "select " + seqName.toUpperCase() + ".NEXTVAL seqvalue from dual";
    	MethodContext methodcontext = MethodContext.getContext();
        String nextValue = "";
        try {
        	wtconnection = (WTConnection) methodcontext.getConnection();
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            pstmt = wtconnection.prepareStatement(querySQL);
            rs = pstmt.executeQuery();
            if (rs.next()) {
            	nextValue = rs.getString("seqvalue");
            }
            rs.close();
            rs = null;

            pstmt.close();
            pstmt = null;
            //result = true;
        } catch (SQLException e) {
            LCSLog.error(CLASS_NAME + "$$$$isSequenceExist$$$$"
                    + e.getMessage());
        } catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        return nextValue;
    }
    /**
     * check the Sequence is Exist
     * @param wtConn
     * @param seqName
     * @return
     */
    public static boolean isSequenceExist(WTConnection wtConn, String seqName) {
        boolean result = false;
        String querySQL = "select sequence_name from user_sequences where sequence_name=? ";
        String sName = "";
        try {
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            pstmt = wtConn.prepareStatement(querySQL);
            pstmt.setString(1, seqName.toUpperCase());
            rs = pstmt.executeQuery();
            if (rs.next()) {
                sName = rs.getString("sequence_name");
                LCSLog.debug(CLASS_NAME + "$$$$isSequenceExist$$$$"
                        + " sequence exist:" + sName.toUpperCase());
                result = true;
            }
            rs.close();
            rs = null;

            pstmt.close();
            pstmt = null;
            //result = true;
        } catch (SQLException e) {
            LCSLog.error(CLASS_NAME + "$$$$isSequenceExist$$$$"
                    + e.getMessage());
            result = false;
        }
        return result;
    }
    /**
     * create Sequence
     * @param wtConn
     * @param seqName
     * @return
     */
    public static boolean createSequence(WTConnection wtConn, String seqName) {
        if (isSequenceExist(wtConn, seqName)) {
            return true;
        }

        boolean result = false;

        String createSQL = "create sequence "
                + seqName.toUpperCase()
                + " minvalue 1 NOMAXVALUE NOCYCLE  start with 100 increment by 1 nocache ";
        try {
            PreparedStatement pstmt = null;
            pstmt = wtConn.prepareStatement(createSQL);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;
            result = true;
        } catch (SQLException e) {
            LCSLog
                    .error(CLASS_NAME + "$$$$createSequence$$$$"
                            + e.getMessage());
            result = false;
        }

        return result;
    }
    
    
    public static WTConnection getWtConnection() {
        try {
            WTConnection conn = (WTConnection) MethodContext.getContext().getConnection();
            return conn;
        } catch (Exception e) {
        	LCSLog.error("获取数据库连接失败", e);
        }
        return null;
    }
    
    
    public static void freeWTConnection(WTConnection wtconnection) {
        if (!wtconnection.isTransactionActive()) {
            MethodContext.getContext().freeConnection();
        }
    }
    
    /**
     * 执行SQL语句
     *
     * @param sql
     * @return
     */
    public static int executeUpdate(String sql) {
        return executeUpdate(sql, new Object[0]);
    }

    /**
     * 执行SQL语句
     *
     * @param sql
     * @param parameters
     *            参数
     * @return
     */
    public static int executeUpdate(String sql, Object[] parameters) {
        WTConnection con = null;
        try {
            con = getWtConnection();
            if (con != null) {
                Transaction transaction = new Transaction();
                transaction.start();
                PreparedStatement stmt = null;
                try {
                    stmt = prepareStatement(sql, parameters, con);
                    int result = stmt.executeUpdate();
                    transaction.commit();
                    transaction = null;
                    return result;
                } catch (SQLException e) {
                	LCSLog.error("执行sql语句错误：" + sql, e);
                } finally {
                    if (stmt != null)
                        con.freeStatement(sql, stmt, true);
                    if (transaction != null) {
                        transaction.rollback();
                        LCSLog.debug("执行以下语句取消事务：" + sql);
                    }
                }
            }
        } catch (Exception e) {
        	LCSLog.error("执行sql语句错误：" + sql, e);
        }
        return -1;
    }
    
    /**
     * 使用sql语句和参数创建statement
     *
     * @param sql
     * @param parameters
     * @param con
     * @return
     * @throws SQLException
     */
    private static PreparedStatement prepareStatement(String sql, Object[] parameters, WTConnection con)
            throws SQLException {
        PreparedStatement stmt;
        stmt = con.prepareStatement(sql);
        for (int i = 1; i <= parameters.length; i++) {
            Object obj = parameters[i - 1];
            if (obj instanceof String) {
                stmt.setString(i, (String) obj);
            }
            if (obj instanceof Date) {
                stmt.setDate(i, (Date) obj);
            }
            if (obj instanceof Integer) {
                stmt.setInt(i, ((Integer) obj).intValue());
            }
            if (obj instanceof Double) {
                stmt.setDouble(i, ((Double) obj).doubleValue());
            }
        }
        return stmt;
    }
    
    
    /**
     * 查询一条数据返回Map
     *
     * @param sql
     * @return
     */
    public static Map queryForMap(String sql) {
        WTConnection conn = getWtConnection();
        if (conn != null) {
            return queryForMap(conn, sql);
        }
        return null;
    }
    
    
    /**
     * 查询一条数据返回Map
     *
     * @param sql
     * @return
     */
    public static Map queryForMap(WTConnection conn, String sql) {
        if (conn == null)
            return null;
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                Map map = new HashMap();
                ResultSetMetaData meta = rs.getMetaData();

                for (int i = 1; i <= meta.getColumnCount(); i++) {
                    map.put(meta.getColumnName(i).toLowerCase(), rs.getObject(i));
                }
                return map;
            }

        } catch (SQLException e) {
        	LCSLog.error("执行sql语句错误：" + sql, e);
            return null;
        } finally {
            if (stmt != null)
                conn.freeStatement(sql, stmt, true);
        }
        return null;
    }
    
    
    
    /**
     * 查询数据返回List<Map>
     *
     * @param sql
     * @return
     */
    public static List queryForList(String sql) {
        WTConnection conn = getWtConnection();
        if (conn != null) {
            return queryForList(conn, sql);
        }
        return new ArrayList();
    }

    /**
     * 查询数据返回List<Map>
     *
     * @param sql
     * @return
     */
    public static List queryForList(WTConnection conn, String sql) {
        PreparedStatement stmt = null;
        List ResultList = new ArrayList();
        try {
            stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            List columnNames = null;
            while (rs.next()) {
                if (columnNames == null) {
                    columnNames = new ArrayList();
                    ResultSetMetaData meta = rs.getMetaData();
                    for (int i = 1; i <= meta.getColumnCount(); i++) {
                        columnNames.add(meta.getColumnName(i).toLowerCase());
                    }
                }
                Map map = new HashMap();

                for (int i = 0; i < columnNames.size(); i++) {
                    map.put(columnNames.get(i), rs.getObject((String) columnNames.get(i)));
                }
                ResultList.add(map);
            }

        } catch (SQLException e) {
        	LCSLog.error("执行sql语句错误：" + sql, e);
        } finally {
            if (stmt != null)
                conn.freeStatement(sql, stmt, true);
        }
        return ResultList;
    }
    
    /**
     * 查询一条数据返回数量
     *
     * @param sql
     * @return
     */
    public static int queryForInt(String sql) {
        WTConnection conn = getWtConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }

        } catch (SQLException e) {
        	LCSLog.error("执行sql语句错误：" + sql, e);
            return 0;
        } finally {
            if (stmt != null)
                conn.freeStatement(sql, stmt, true);
        }
        return 0;
    }

    /**
     * 查询一条数据返回指定类的对象
     * 
     * @param sql
     * @return
     */
    public static Object queryForObject(String sql, Class clazz) {
        return queryForObject(sql, new Object[0], clazz);
    }

    /**
     * 查询一条数据返回指定类的对象
     * 
     * @param sql
     * @return
     */
    public static Object queryForObject(String sql, Object[] parameters, Class clazz) {
        WTConnection conn = getWtConnection();
        PreparedStatement stmt = null;
        try {
            stmt = prepareStatement(sql, parameters, conn);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                Object obj = rs.getObject(1);
                if(clazz.isInstance(obj)) return obj;
            }

        } catch (SQLException e) {
        	LCSLog.error("执行sql语句错误：" + sql, e);
            return null;
        } finally {
            if (stmt != null)
                conn.freeStatement(sql, stmt, true);
        }
        return null;
    }

    /**
     * 查询数据返回List<Map>
     *
     * @param sql
     * @return
     */
    public static List queryForObjectList(String sql) {
        WTConnection conn = getWtConnection();
        if (conn != null) {
            return queryForObjectList(conn, sql);
        }
        return new ArrayList();
    }

    /** 
     * query for object list.
     * @param sql 
     * @param parameters 
     * @return 
     */
    public static List queryForObjectList(String sql,Object[] parameters) {
        WTConnection conn = getWtConnection();
        if (conn != null) {
            return queryForObjectList(conn, sql,parameters);
        }
        return new ArrayList();
    }

    /**
     * 查询数据返回List<Map>
     *
     * @param sql
     * @return
     */
    public static List queryForObjectList(WTConnection conn,String sql,Object[] parameters) {
            PreparedStatement stmt = null;
            List ResultList = new ArrayList();
            try {
                    if (parameters == null)
                            stmt = conn.prepareStatement(sql);
                    else
                            stmt = prepareStatement(sql,parameters,conn);
                    ResultSet rs = stmt.executeQuery();
                    while (rs.next()) {
                            ResultList.add(rs.getObject(1));
                    }
                    rs.close();
            } catch (SQLException e) {
            		LCSLog.error("执行sql语句错误：" + sql, e);
            } finally {
                    if (stmt != null)
                            conn.freeStatement(sql, stmt, true);
            }
            return ResultList;
    }

    /**
     * 查询数据返回List<Map>
     *
     * @param sql
     * @return
     */
    public static List queryForObjectList(WTConnection conn, String sql) {
       return queryForObjectList(conn,sql,null); 
    }
    
    
}
